-- Gets a list of all of the stored procedures and functions in the database and 
-- grant EXECUTE rights for each stored procedure or scalar function for the 
-- <DatabaseName>Role role.  If the function is a table-value function, then SELECT
-- rights are granted for the role.
-- In order for this script to run, you must be in the context of a database other
-- than the master database.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SetStoredProcedurePermissions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SetStoredProcedurePermissions]
GO

PRINT 'Creating sp_SetStoredProcedurePermissions...'
GO

CREATE PROCEDURE dbo.sp_SetStoredProcedurePermissions
(
	@role_name sysname
)
AS
IF DB_NAME() <> 'master'
BEGIN
	-- Need to use a temporary table here instead of a table variable
	-- because you cannot insert into a table variable from a stored procedure.
	CREATE TABLE #stored_procedures
		(
		procedure_id int IDENTITY(1,1) NOT NULL,
		procedure_qualifier sysname,
		procedure_owner sysname,
		procedure_name nvarchar(134),
		num_input_params int,
		num_output_params int,
		num_result_sets int,
		remarks varchar(254),
		procedure_type smallint
		)
	INSERT INTO #stored_procedures
		(
		procedure_qualifier,
		procedure_owner,
		procedure_name,
		num_input_params,
		num_output_params,
		num_result_sets,
		remarks,
		procedure_type
		)
	EXEC sp_stored_procedures
	
	DECLARE
		@counter int,
		@sp_count int,
		@procedure_name nvarchar(134),
		@grant_sql varchar(1000),
		@parsed_procedure_name nvarchar(134),
		@is_stored_procedure bit,
		@is_system_stored_procedure bit,
		@object_id int,
		@is_table_function bit
	
	SET @counter = 0
	SELECT @sp_count = COUNT(*) FROM #stored_procedures
	
	WHILE @counter < @sp_count
	BEGIN
		SET @counter = @counter + 1
		SELECT @procedure_name = procedure_name FROM #stored_procedures WHERE procedure_id = @counter
		SET @parsed_procedure_name = SUBSTRING(@procedure_name, 1, LEN(@procedure_name) - 2)
		SET @is_stored_procedure = CAST(SUBSTRING(@procedure_name, LEN(@procedure_name), 1) AS bit)
		SET @is_system_stored_procedure =  
			CASE 
				WHEN SUBSTRING(@parsed_procedure_name, 1, 2) = 'sp' THEN 1 
				WHEN SUBSTRING(@parsed_procedure_name, 1, 2) = 'xp' THEN 1 
				WHEN SUBSTRING(@parsed_procedure_name, 1, 2) = 'dm' THEN 1 
				WHEN SUBSTRING(@parsed_procedure_name, 1, 2) = 'fn' THEN 1 
				ELSE 0 
			END
		SET @object_id = (select id from dbo.sysobjects where id = object_id(@parsed_procedure_name))
		SET @is_table_function = OBJECTPROPERTY(@object_id, N'IsTableFunction')
		IF @is_system_stored_procedure = 0
		BEGIN
			IF @is_stored_procedure = 1 OR @is_table_function = 0
			BEGIN
				SET @grant_sql = 'GRANT EXECUTE ON ' + @parsed_procedure_name + ' TO ' + @role_name
				PRINT 'Granting EXECUTE rights on ' + @parsed_procedure_name + ' to ' + @role_name + '.'
			END
			ELSE
			BEGIN
				SET @grant_sql = 'GRANT SELECT ON ' + @parsed_procedure_name + ' TO ' + @role_name
				PRINT 'Granting SELECT rights on ' + @parsed_procedure_name + ' to ' + @role_name + '.'
			END
			EXECUTE(@grant_sql)
		END
	END
	DROP TABLE #stored_procedures
END
GO